package com.ars.common.dao;

import com.ars.common.beans.User.User;
import com.ars.common.utils.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

//将所有的用户信息封装出来,采用键值对，id为键，User对象为值
//保持数据按学号有序排列，采用Treemap
public class userInformation {
    public static ArrayList< User> getUserInformation() throws SQLException, ClassNotFoundException
    {
        ArrayList<User> arr = new ArrayList();

        String sql = "-- 左连接查询\n" +
                "SELECT u.userID, u.mailbox, u.name, u.phone, s.sex, r.role, a.AdminGrade, a.id AS AdminID, u.password\n" +
                "FROM user u\n" +
                "LEFT JOIN role r ON u.role = r.id\n" +
                "LEFT JOIN sex s ON u.sex = s.id\n" +
                "LEFT JOIN admin a ON u.isAdmin = a.id\n" +
                "LIMIT 0, 1000;\n";

        PreparedStatement ps = DBUtil.getConnection().prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while(rs.next())
        {
            User user = new User();
            user.setUserID(rs.getString("userID"));
            user.setPassword(rs.getString("password"));
            user.setMailbox(rs.getString("mailbox"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            user.setSex(rs.getString("sex"));
            user.setRole(rs.getString("role"));
            user.setAdminID(rs.getString("AdminID"));//管理员等级编号
            user.setAdminName(rs.getString("AdminGrade"));//管理员等级
            arr.add(user);
        }
        return arr;
    }
}
